package dao.daocontrollers;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import org.apache.log4j.Logger;

import api.entitites.IGarage;
import api.workerswithdb.IDAOGarage;

public class DAOGarage extends GenericDAO implements IDAOGarage {

	private static final String FREE_PLACES_ON_SERVICE = "select count(id_garage) from garages where fullness_garage=false";
	private static final String GET_COUNT_GARAGE = "select count(id_garage) from (select id_garage from garages where fullness_garage=false union select id_garage from orders where deadline_date<?)s";
	private static final String GET_COUNT_MASTER = "select count(id_master) from (select id_master from masters where employment_master=false union select id_master from orders where deadline_date<?)s";
	private static final String DELIMITER = " ";
	private static final String GARAGE_INSERT="insert into garages(number_garage,fullness_garage) values(?,?)";
	private static final String READ_GARAGE= "select * from garages where number_garage=?";
	private static final String DELETE_GARAGE="delete from garages where id_garage=?";
	private static final String ID_GARAGE= "select id_garage from garages where number_garage=?";
	private static final String UPDATE_GARAGE="update garages set fullness_garage=? where id_garage=?";

	
	private Logger log = Logger.getLogger(DAOGarage.class);
	

	
	public Boolean insertGarageToDB(IGarage garage) {
		Boolean answer = false;
		try {
			super.insert(GARAGE_INSERT,garage.getNumberGarage(), garage.getEmptyGarage());
			answer = true;
		} catch (SQLException sqle) {
			log.error(DBMessanger.GARAGE_EXISTS);
		}
		return answer;
	}
	
	
	
	public void updateGarage(Connection connection,Boolean fullnessGarage,Integer idGarage){
		try{
			super.update(connection,UPDATE_GARAGE, fullnessGarage,idGarage);
		}catch(SQLException sqle){
			log.error(DBMessanger.ERROR);
		}
		
	}
	

	public IGarage getGarageFromDb(Integer numberGarage) {
		ResultSet garageFromDbResult;
		try {
			garageFromDbResult = super.read(READ_GARAGE,numberGarage);
			return EntitiesGenerator.parserGarage(garageFromDbResult);
		} catch (SQLException sqle) {
			log.error(DBMessanger.GARAGE_NOT_EXISTS);
			return null;
		}
	}

	public Integer getEmptyGarage() {
		PreparedStatement freePlaces = null;
		ResultSet countFreePlacesResult = null;
		Integer countPlace = null;
		Connection connection=super.connectorTDB.getConnection();
		try {
			freePlaces = connection.prepareStatement(FREE_PLACES_ON_SERVICE);
			countFreePlacesResult = freePlaces.executeQuery();
			countFreePlacesResult.next();
			countPlace = countFreePlacesResult.getInt(1);
		} catch (SQLException sqle) {
			log.error(DBMessanger.ERROR);
		} finally {
			try {
				freePlaces.close();
				countFreePlacesResult.close();
				connection.close();
			} catch (SQLException sqle) {
				log.error(DBMessanger.ERROR);
			}
		}
		return countPlace;

	}

	public String getEmptyPlacesOnDate(Date date) {
		PreparedStatement countGarages = null;
		PreparedStatement countMasters = null;
		ResultSet countGaragesResult = null;
		ResultSet countMastersResult = null;
		String result = null;
		Connection connection=super.connectorTDB.getConnection();
		try {
			connection.setAutoCommit(false);
			countGarages = connection.prepareStatement(GET_COUNT_GARAGE);
			countMasters = connection.prepareStatement(GET_COUNT_MASTER);
			Integer numbersMaster = null;
			Integer numberGarage = null;
			java.sql.Date dateSql = EntitiesGenerator.conversionDate(date);
			countMasters.setDate(1, dateSql);
			countMastersResult = countMasters.executeQuery();
			while (countMastersResult.next()) {
				numbersMaster = countMastersResult.getInt(1);
			}
			countGarages.setDate(1, dateSql);
			countGaragesResult = countGarages.executeQuery();
			while (countGaragesResult.next()) {
				numberGarage = countGaragesResult.getInt(1);
			}
			result = numbersMaster + DELIMITER + numberGarage;
			connection.commit();

		} catch (SQLException sqlE) {
			log.error(DBMessanger.ERROR);
			try{
				connection.rollback();
			}catch(SQLException sql){
				log.error(DBMessanger.ERROR);
			}
		} finally {
			try {
				countGarages.close();
				countMasters.close();
				countGaragesResult.close();
				countMastersResult.close();
				connection.close();
			} catch (SQLException sqle) {
				log.error(DBMessanger.ERROR);
			}
		}
		return result;
	}

	public Integer getIdGarage(Integer numberGarage) {
		try {
			Integer idGarageResult = super.getObjectId(ID_GARAGE,numberGarage);
			return idGarageResult;
		} catch (SQLException sqle) {
			log.error(DBMessanger.GARAGE_NOT_EXISTS);
			return null;
		}
	}

	public Boolean deleteGarage(Integer numberGarage) {
		Boolean answer = false;
		try {
			super.deleteObject(DELETE_GARAGE,getIdGarage(numberGarage));
			answer = true;
		} catch (SQLException sqle) {
			log.error(DBMessanger.ERROR);
		}
		return answer;
	}

}
